﻿<?xml version="1.0" encoding="utf-8" ?>
<commands>
  <attachment>
    <GetAttachmetnsForUserStory>
      <![CDATA[
              SELECT [ID],
                     [FILE_NAME],
                     [PATH]
              FROM [ATTACHMENT]
              WHERE [USER_STORY_ID]=@userStoryID
              ]]>
    </GetAttachmetnsForUserStory>
  </attachment>
  <bli>
    <CreateBli>
      <![CDATA[
        INSERT INTO [BLI](                    
          [NAME],
          [BLI_TYPE_ID],
          [DESCRIPTION],
          [USER_STORY_ID],
          [BLI_STATE_ID],
          [ESTIMATION],
          [PRIORITY]
        )VALUES(
          @name,
          @typeID,
          @description,
          @userStoryId,
          @bliStateId,
          @estimation,
          @priority
        )
      ]]>
    </CreateBli>
    <GetLastInsertedBliID>
      <![CDATA[ SELECT IDENT_CURRENT('BLI') ]]>
    </GetLastInsertedBliID>
    <UpdateBli>
      <![CDATA[
              UPDATE [BLI]
              SET [NAME] = @name,
                  [DESCRIPTION] = @description,
                  [USER_STORY_ID] = @userStoryID,
                  [BLI_TYPE_ID] = @typeID,
                  [BLI_STATE_ID] = @stateId,
                  [ESTIMATION] = @estimation,
                  [PRIORITY] = @priority,
                  [CHANGED] = getdate()
              WHERE [ID] = @bliId
              ]]>
    </UpdateBli>
    <DeleteBli>
      <![CDATA[
              DELETE FROM [BLI]
              WHERE [ID] = @bliID
              ]]>
    </DeleteBli>
    <GetBlis>
      <![CDATA[
        SELECT 
          [BLI].*
          ,[BLI_STATE].[NAME] AS [STATE_NAME]
          ,[BLI_STATE].[ID] AS [STATE_ID]
          ,[BLI_TYPE].[ID] AS [TYPE_ID]
        FROM 
          [BLI]
          JOIN [USER_STORY] ON ([USER_STORY].[ID] = [BLI].[USER_STORY_ID])
          JOIN [SPRINT] ON ([SPRINT].[ID] = [USER_STORY].[SPRINT_ID])
          JOIN [RELEASE] ON ([RELEASE].[ID] = [SPRINT].[RELEASE_ID])
          JOIN [PROJECT] ON ([PROJECT].[ID] = [RELEASE].[PROJECT_ID])
          JOIN [BLI_STATE] ON ([BLI_STATE].[ID] = [BLI].[BLI_STATE_ID])
          JOIN [BLI_TYPE] ON ([BLI_TYPE].[ID] = [BLI].[BLI_TYPE_ID])
        WHERE
              ([BLI].[ID] = @bliID OR @bliID = -1)
          AND ([USER_STORY].[ID] = @userStoryID OR @userStoryID = -1)
          AND ([SPRINT].[ID] = @sprintID OR @sprintID = -1)
          AND ([RELEASE].[ID] = @releaseID OR @releaseID = -1)
          AND ([PROJECT].[ID] = @projectID OR @projectID = -1)
          AND (@taskID = -1 OR EXISTS(
            SELECT 1 FROM [TASK] WHERE [TASK].[ID] = @taskID AND [TASK].[BLI_ID] = [BLI].[ID]
          ))
      ]]>
    </GetBlis>
    <GetBlisForUserStory>
      <![CDATA[
              SELECT b.[ID],
                     b.[NAME],
                     bt.[ID],
                     b.[DESCRIPTION],
                     b.[USER_STORY_ID],
                     bs.[NAME] as [STATE_NAME],
                     b.[ESTIMATION],
                     b.[PROGRESS]
              FROM [BLI] b, [BLI_STATE] bs, [BLI_TYPE] bt
              WHERE [USER_STORY_ID] = @userStoryID
                    bs.[ID] = b.[BLI_STATE_ID] AND 
                    bt.[ID] = b.[BLI_TYPE_ID]
              ]]>
    </GetBlisForUserStory>
    <GetBliTypes>
      <![CDATA[
            SELECT [ID],
                   [NAME]
            FROM [BLI_TYPE]
            ]]>
    </GetBliTypes>
    <GetBliByTaskID>
      <![CDATA[
              SELECT b.[ID],
                     b.[NAME],
                     b.[BLI_TYPE_ID],
                     b.[DESCRIPTION],
                     b.[USER_STORY_ID],
                     b.[BLI_STATE_ID] as [STATE_ID],
                     st.[NAME] as [STATE_NAME],
                     b.[ESTIMATION],
                     b.[PROGRESS],
                     b.[PRIORITY]
              FROM [BLI] b, 
                   [TASK] t,
                   [BLI_STATE] st
              WHERE t.BLI_ID = b.ID AND 
                    t.ID = @taskId AND
                    b.BLI_STATE_ID = st.ID
              ]]>
    </GetBliByTaskID>
  </bli>
  <dashboard>
    <GetLayout>
      <![CDATA[
              SELECT [LAYOUT]
              FROM [DASHBOARD]
              WHERE [USER_ID] = @userID
              ]]>
    </GetLayout>
  </dashboard>
	<userSettings>
		<UpdateSettingsForLoggedUser>
			<![CDATA[
        UPDATE [USER_SETTINGS] 
				SET [LANGUAGE]=@language, [THEME]=@theme
        WHERE [USER_ID] = @userID
        ]]>
		</UpdateSettingsForLoggedUser>
		<GetSettingsForUser>
			<![CDATA[
        SELECT [ID], [LANGUAGE], [THEME]
        FROM [USER_SETTINGS] 
        WHERE [USER_ID] = @userID
        ]]>
		</GetSettingsForUser>
    <CreateDefaultSettings>
      <![CDATA[
        INSERT [USER_SETTINGS]
          (
            [LANGUAGE],
            [THEME],
            [USER_ID]
          )
        VALUES
          (
            'en-EN',
            'Default',
            @userID          
          )
          
            
        ]]>
    </CreateDefaultSettings>
	</userSettings>
	<burndownchart>
    <GetSource>
      <![CDATA[
        SELECT [DATE],
	              SUM([STORY_POINTS]) AS [STORY_POINTS],
	             [TASK_STATE_ID]
        FROM [WORK_PROGRESS]
        WHERE [SPRINT_ID] = @sprintID
        GROUP BY [DATE],[TASK_STATE_ID]
        ORDER BY [DATE],[TASK_STATE_ID]      
        ]]>
    </GetSource>
  </burndownchart>
  <column>
    <GetColumns>
      <![CDATA[
              SELECT [ID], 
                     [ORDER], 
                     [WIDTH]
              FROM [WIDGET_COLUMN]
              WHERE [TAB_ID]=@tabID
              ORDER BY [ORDER] ASC
              ]]>
    </GetColumns>
    <GetColumnForWidget>
      <![CDATA[
              SELECT [ID], 
              [ORDER], [WIDTH]
              FROM [WIDGET_COLUMN]
              WHERE [WIDGET_COLUMN].[ID]=(
                                    SELECT [WIDGET].[COLUMN_ID] 
                                    FROM [WIDGET] 
                                    WHERE [WIDGET].[ID] = @widgetID
                                  )
              ]]>
    </GetColumnForWidget>
    <DeleteColumn>
      <![CDATA[
              DELETE FROM [WIDGET_COLUMN] 
              WHERE [id] = @widgetColumnID
              ]]>
    </DeleteColumn>
    <UpdateColumn>
      <![CDATA[
      UPDATE [WIDGET_COLUMN]
      SET [ORDER] = @order
          ,[WIDTH] = @width
 WHERE [ID] = @columnID
      ]]>
    </UpdateColumn>
    <AddColumn>
      <![CDATA[
              INSERT INTO [WIDGET_COLUMN] 
                 (
                   [ORDER],
                   [WIDTH],
                   [TAB_ID]
                 ) 
              VALUES
                 (
                   @order,
                   @width,
                   @tabID
                 )
              ]]>
    </AddColumn>
    <GetLastInsertedColumnID>
      <![CDATA[ SELECT IDENT_CURRENT('WIDGET_COLUMN') ]]>
    </GetLastInsertedColumnID>
  </column>
  <dailyScrum>
    <GetAllDailyScrumDetails>
      <![CDATA[
              SELECT d.[ID],
                     d.[DATE],
                     d.[TASK_ID],
                     d.[DID],
                     d.[TO_DO],
                     d.[PROBLEMS],
                     d.[STORY_POINTS],
                     d.[COMMENTS],
                     d.[USER_ID],
                     t.[NAME]
               FROM [DAILY_SCRUM] d, 
                    [TASK] t
               WHERE d.[USER_ID] = @userID AND 
                     d.[TASK_ID] = t.[ID]
               ]]>
    </GetAllDailyScrumDetails>
    <InsertDailyScrum>
      <![CDATA[
              INSERT INTO [DAILY_SCRUM]
                 (
                    [DATE],
                    [TASK_ID],
                    [DID],
                    [TO_DO],
                    [PROBLEMS],
                    [STORY_POINTS],
                    [COMMENTS],
                    [USER_ID]
                 )
              VALUES
                 (
                    @day,
                    @taskID,
                    @did,
                    @toDo,
                    @problems,
                    @burnDown,
                    @comments,
                    @userID
                 )
              ]]>
    </InsertDailyScrum>
    <UpdateDailyScrum>
      <![CDATA[
              UPDATE [DAILY_SCRUM]
              SET [DATE] = @day,
                  [TASK_ID] = @taskID,
                  [DID] = @did,
                  [TO_DO] = @toDo,
                  [PROBLEMS] = @problems,
                  [STORY_POINTS] = @burnDown,
                  [COMMENTS] = @comments,
                  [USER_ID] = @userID
              WHERE [ID] = @id
              ]]>
    </UpdateDailyScrum>
    <DeleteDailyScrum>
      <![CDATA[
              DELETE FROM [DAILY_SCRUM]
              WHERE [ID] = @id
              ]]>
    </DeleteDailyScrum>
    <GetDailyScrumsID>
      <![CDATA[ 
        SELECT 
          [DAILY_SCRUM].* 
        FROM 
          [DAILY_SCRUM]
         WHERE ID = (SELECT IDENT_CURRENT('DAILY_SCRUM') )
      ]]>
    </GetDailyScrumsID>
  </dailyScrum>
  <project>
    <CreateProject>
      <![CDATA[
        INSERT INTO [PROJECT](
          [NAME],
          [DESCRIPTION],
          [STATE_ID], 
          [CUSTOMER], 
          [START_DATE],
          [ESTIMATION]
        )VALUES(
          @name,
          @description,
          @stateId,  
          @customer, 
          @startDate,
          @estimation
        )
      ]]>
    </CreateProject>
    <GetLastInsertedProjectID>
      <![CDATA[ SELECT IDENT_CURRENT('PROJECT') ]]>
    </GetLastInsertedProjectID>
    <UpdateProject>
      <![CDATA[
        UPDATE 
          [PROJECT]
        SET
          [NAME] = @name,
          [DESCRIPTION] = @description,
          [STATE_ID] = @stateId,
          [CUSTOMER] = @customer,
          [START_DATE] = @startDate,
          [ESTIMATION] = @estimation,
          [CHANGED] = getdate()
        WHERE 
          [ID]=@id
      ]]>
    </UpdateProject>
    <DeleteProject>
      <![CDATA[
        DELETE FROM [PROJECT]
        WHERE [ID]=@projectId
      ]]>
    </DeleteProject>
    <GetAllProjects>
      <![CDATA[
        SELECT 
          [PROJECT].*,
          [PROJECT_STATE].[ID] as [STATE], 
          [PROJECT_STATE].[NAME] as [STATE_NAME]
        FROM 
          [PROJECT]
          JOIN [PROJECT_STATE] ON ([PROJECT].[STATE_ID] = [PROJECT_STATE].[ID])
        WHERE 
              ([PROJECT].[ID] = @projectID OR @projectID = -1)
          AND (@releaseID = -1 OR EXISTS(
            SELECT 1 FROM [RELEASE] 
            WHERE [RELEASE].[PROJECT_ID] = [PROJECT].[ID] AND [RELEASE].[ID] = @releaseID)
          )
      ]]>
    </GetAllProjects>
    <GetProjectById>
      <![CDATA[
      SELECT
        p.[ID] as PROJECT_ID, 
        ps.[ID] as STATE_ID, 
        ps.[NAME] as STATE_NAME, 
        p.[NAME] as NAME, 
        p.[CUSTOMER] as CUSTOMER, 
        p.[START_DATE] as START_DATE
      FROM 
        [PROJECT] p, 
        [PROJECT_STATE] ps
      WHERE
        p.STATE_ID = ps.ID AND 
        p.ID = @projectId
      ]]>
    </GetProjectById>
    <GetProjectByRelease>
      <![CDATA[
              SELECT [PROJECT].[ID],
                     [PROJECT].[STATE_ID],
                     [PROJECT].[NAME],
                     [PROJECT].[CUSTOMER],
                     [PROJECT].[START_DATE],
                     [PROJECT_STATE].[NAME]
                FROM [PROJECT] INNER JOIN [PROJECT_STATE] ON [PROJECT].[STATE_ID] = [PROJECT_STATE].[ID]
                               
                WHERE [RELEASE].[ID]=@releaseID
                ]]>
    </GetProjectByRelease>
    <GetProjectByTeam>
      <![CDATA[
              SELECT [PROJECT].*,
                     [PROJECT_STATE].[ID] as [STATE], 
                     [PROJECT_STATE].[NAME] as [STATE_NAME]
              FROM [PROJECT] INNER JOIN [PROJECT_STATE] ON [PROJECT].[STATE_ID] = [PROJECT_STATE].[ID]
                             INNER JOIN [TEAM] ON [TEAM].[PROJECT_ID] = [PROJECT].[ID]
              WHERE [TEAM].[ID]=@teamID
              ]]>
    </GetProjectByTeam>
  </project>
  <release>
    <CreateRelease>
      <![CDATA[
        INSERT INTO [RELEASE](
          [NAME],
          [VERSION],
          [PROJECT_ID],
          [ESTIMATION]
        )VALUES(
          @name,
          @version,
          @projectId,
          @estimation
        )
      ]]>
    </CreateRelease>
    <GetLastInsertedReleaseID>
      <![CDATA[ SELECT IDENT_CURRENT('RELEASE') ]]>
    </GetLastInsertedReleaseID>
    <UpdateRelease>
      <![CDATA[
        UPDATE 
          [RELEASE]
        SET  
          [NAME] = @name,
          [VERSION] = @version,
          [PROJECT_ID] = @projectId,
          [ESTIMATION] = @estimation,
          [CHANGED] = getdate()
        WHERE 
          [ID] = @releaseId
      ]]>
    </UpdateRelease>
    <DeleteRelease>
      <![CDATA[
        DELETE FROM [RELEASE]
        WHERE ID = @releaseId
      ]]>
    </DeleteRelease>
    <GetAllReleases>
      <![CDATA[
        SELECT 
          [RELEASE].*
        FROM 
          [PROJECT]
          JOIN [RELEASE] ON ([RELEASE].[PROJECT_ID] = [PROJECT].[ID])
        WHERE
              (@projectID = -1 OR [PROJECT].[ID] = @projectID)
          AND (@releaseID = -1 OR [RELEASE].[ID] = @releaseID)
          AND (@sprintID = -1 OR EXISTS(
              SELECT 1 FROM [SPRINT] WHERE [SPRINT].[ID] = @sprintID 
              AND [SPRINT].[RELEASE_ID] = [RELEASE].[ID]
            )
          )
      ]]>
    </GetAllReleases>
  </release>
  <sprint>
    <CreateSprint>
      <![CDATA[
        INSERT INTO [SPRINT](
          [NAME],
          [DESCRIPTION],
          [FROM_DATE],
          [TO_DATE],
          [RELEASE_ID],
          [SPRINT_STATE_ID],
          [FUNC_EST],
          [OTHR_EST],
          [TECH_EST],
          [ESTIMATION]
        )VALUES(
          @name,
          @description,
          @fromDate,
          @toDate,
          @releaseId,
          @sprintStateId,
          @funcEst,
          @othrEst,
          @techEst,
          @estimation
        )
      ]]>
    </CreateSprint>
    <GetLastInsertedSprintId>
      <![CDATA[ SELECT IDENT_CURRENT('SPRINT') ]]>
    </GetLastInsertedSprintId>
    <UpdateSprint>
      <![CDATA[
        UPDATE 
          [SPRINT]
        SET 
          [NAME] = @name,
          [DESCRIPTION] = @description,
          [FROM_DATE] = @fromDate,
          [TO_DATE] = @toDate,
          [RELEASE_ID] = @releaseId,
          [SPRINT_STATE_ID] = @sprintStateId,
          [FUNC_EST] = @funcEst,
          [OTHR_EST] = @othrEst,
          [TECH_EST] = @techEst,
          [ESTIMATION] = @estimation,
          [CHANGED] = getdate()
        WHERE 
          [ID] = @sprintId
      ]]>
    </UpdateSprint>
    <DeleteSprint>
      <![CDATA[
        DELETE FROM [SPRINT]
        WHERE ID = @sprintId
      ]]>
    </DeleteSprint>
    <GetAllSprints>
      <![CDATA[
        SELECT 
          [SPRINT].*,
          [SPRINT_STATE].[ID] AS [STATE_ID],
          [SPRINT_STATE].[NAME] AS [STATE_NAME]
        FROM 
          [PROJECT]
          JOIN [RELEASE] ON ([RELEASE].[PROJECT_ID] = [PROJECT].[ID])
          JOIN [SPRINT] ON ([SPRINT].[RELEASE_ID] = [RELEASE].[ID])
          JOIN [SPRINT_STATE] ON ([SPRINT].[SPRINT_STATE_ID] = [SPRINT_STATE].[ID])
        WHERE
              ([PROJECT].[ID] = @projectID OR @projectID = -1)
          AND ([RELEASE].[ID] = @releaseID OR @releaseID = -1)
          AND ([SPRINT].[ID] = @sprintID OR @sprintID = -1)
          AND (@userStoryID = -1 OR EXISTS(
            SELECT 1 FROM [USER_STORY] WHERE [USER_STORY].[ID] = @userStoryID 
            AND [USER_STORY].[SPRINT_ID] = [SPRINT].[ID]
          ))
      ]]>
    </GetAllSprints>
    <GetAllFutureSprints>
      <![CDATA[
        SELECT 
          [SPRINT].*,
          [SPRINT_STATE].[ID] AS [STATE_ID],
          [SPRINT_STATE].[NAME] AS [STATE_NAME]
        FROM 
          [SPRINT]
          JOIN [SPRINT_STATE] ON ([SPRINT].[SPRINT_STATE_ID] = [SPRINT_STATE].[ID])
        WHERE
          ([SPRINT].[FROM_DATE] > getdate() OR @future = 0)
      ]]>
    </GetAllFutureSprints>
    <GetAllSprintsForProject>
      <![CDATA[
              SELECT sprint.[ID] as ID,
                     sprint.[NAME] as NAME,
                     sprint.[DESCRIPTION] as DESCRIPTION,
                     sprint.[FROM_DATE] as FROM_DATE,
                     sprint.[TO_DATE] as TO_DATE,
                     sprint.[SPRINT_STATE_ID] as STATE_ID,
                     state.[NAME] as STATE_NAME,
                     sprint.[ESTIMATION] as ESTIMATION
              FROM [RELEASE] release, 
                   [SPRINT] sprint, 
                   [SPRINT_STATE] state
              WHERE release.[PROJECT_ID] = @projectID AND
                    sprint.[RELEASE_ID] = release.[ID] AND 
                    state.[ID] = sprint.[SPRINT_STATE_ID]
              ]]>
    </GetAllSprintsForProject>
    <GetAllSprintsForRelease>
      <![CDATA[
              SELECT sprint.[ID] as ID,
                     sprint.[NAME] as NAME,
                     sprint.[DESCRIPTION] as DESCRIPTION,
                     sprint.[FROM_DATE] as FROM_DATE,
                     sprint.[TO_DATE] as TO_DATE,
                     sprint.[SPRINT_STATE_ID] as STATE_ID,
                     state.[NAME] as STATE_NAME,
                     sprint.[ESTIMATION] as ESTIMATION
              FROM [SPRINT] sprint, [SPRINT_STATE] state
              WHERE sprint.[RELEASE_ID] = @releaseId AND 
                    state.[ID] = sprint.[SPRINT_STATE_ID]
              ]]>
    </GetAllSprintsForRelease>
    <GetSprintById>
      <![CDATA[
              SELECT sprint.[ID] as ID,
                     sprint.[NAME] as NAME,
                     sprint.[DESCRIPTION] as DESCRIPTION,
                     sprint.[FROM_DATE] as FROM_DATE,
                     sprint.[TO_DATE] as TO_DATE,
                     sprint.[SPRINT_STATE_ID] as STATE_ID,
                     state.[NAME] as STATE_NAME,
                     r.[ID] as RELEASE_ID,
                     r.[NAME] as RELEASE_NAME,
                     sprint.[ESTIMATION] as ESTIMATION
              FROM [RELEASE] r, 
                   [SPRINT] sprint,
                   [SPRINT_STATE] state
              WHERE sprint.[ID] = @sprintId AND
                    r.[ID] = sprint.[RELEASE_ID] AND
                    state.[ID] = sprint.[SPRINT_STATE_ID]
              ]]>
    </GetSprintById>
    <GetSprintByUserStory>
      <![CDATA[
              SELECT sprint.[ID],
                     sprint.[NAME] as NAME,
                     sprint.[DESCRIPTION],
                     sprint.[FROM_DATE],
                     sprint.[TO_DATE],                     
                     sprint.[SPRINT_STATE_ID] as STATE_ID,
                     sprint.[RELEASE_ID],
                     state.[NAME] as STATE_NAME,
                     sprint.[ESTIMATION]
              FROM [USER_STORY] us,
                   [SPRINT] sprint,
                   [SPRINT_STATE] state
              WHERE us.[ID] = @userStoryID AND
                    us.[SPRINT_ID] = sprint.[ID] AND
                    state.[ID] = sprint.[SPRINT_STATE_ID]
             ]]>
    </GetSprintByUserStory>
    <GetValueForTasks>
      <![CDATA[
        SELECT count(t.[ID])
        FROM [TASK] t JOIN [BLI] b ON t.[BLI_ID] = b.[ID]
					            JOIN [USER_STORY] us ON b.[USER_STORY_ID] =  us.[ID]
                      JOIN [SPRINT] s ON us.[SPRINT_ID] = s.[ID]        
        WHERE s.[ID] = @sprintID AND
              t.[TASK_STATE_ID] = @taskStateID
        ]]>
    </GetValueForTasks>
  </sprint>
  <state>
    <CreateState>
      <![CDATA[
              INSERT INTO [@tableName]
                 (
                  [STATE_NAME]
                 )
              VALUES
                 (
                  @name
                 )
              ]]>
    </CreateState>
    <UpdateState>
      <![CDATA[
              UPDATE [@tableName]
              SET [STATE_NAME] = @name
              WHERE [ID]=@id
              ]]>
    </UpdateState>
    <DeleteState>
      <![CDATA[
               DELETE FROM [@tableName]
               WHERE [ID]=@id
               ]]>
    </DeleteState>
    <GetAllProjectStates>
      <![CDATA[
              SELECT [ID] as ID, 
                     [NAME] as STATE_NAME
              FROM [PROJECT_STATE]
              ]]>
    </GetAllProjectStates>
    <GetAllSprintStates>
      <![CDATA[
              SELECT [ID] as ID, 
                     [NAME] as STATE_NAME 
              FROM [SPRINT_STATE]
              ]]>
    </GetAllSprintStates>
    <GetAllBliStates>
      <![CDATA[
              SELECT [ID] as ID,
                     [NAME] as STATE_NAME
              FROM [BLI_STATE]
              ]]>
    </GetAllBliStates>
    <GetAllUserStoryStates>
      <![CDATA[
              SELECT [ID] as ID, 
                     [NAME] as STATE_NAME
              FROM [USER_STORY_STATE]
              ]]>
    </GetAllUserStoryStates>
    <GetAllTaskStates>
      <![CDATA[
              SELECT [ID] as ID, 
                     [NAME] as STATE_NAME 
              FROM [TASK_STATE]
              ]]>
    </GetAllTaskStates>
  </state>
  <tab>
    <GetTabs>
      <![CDATA[
              SELECT [WIDGET_TAB].*
              FROM [WIDGET_TAB]
              WHERE [USER_ID]=@userID
              ORDER BY [ORDER] ASC
              ]]>
    </GetTabs>
    <GetFirstUserTab>
      <![CDATA[
        SELECT [WIDGET_TAB].*
        FROM [WIDGET_TAB]
        WHERE [USER_ID] = @userID AND
              [ORDER] = 0
        ]]>
    </GetFirstUserTab>
    <GetActiveTab>
      <![CDATA[
              SELECT [WIDGET_TAB].*
              FROM [WIDGET_TAB]
              WHERE [USER_ID]=@userID AND 
                    [ACTIVE]=1
              ]]>
    </GetActiveTab>
    <SetActive>
      <![CDATA[
              UPDATE [WIDGET_TAB]
              SET [ACTIVE]=0
              WHERE [USER_ID]=@userID

              UPDATE [WIDGET_TAB]
              SET [ACTIVE]=1
              WHERE [ID]=@tabID
              ]]>
    </SetActive>
    <CreateTab>
      <![CDATA[          
            UPDATE [WIDGET_TAB]
            SET [ACTIVE]=0
            WHERE [USER_ID]=@userID
        
            INSERT INTO [WIDGET_TAB]
               (
                 [NAME],
                 [USER_ID],
                 [ORDER],
                 [ACTIVE]
               )
            VALUES
               (
                 @name,
                 @userID,
                 @order,
                 1
               )
            
            INSERT INTO [WIDGET_COLUMN]
               (
                 [ORDER],
                 [WIDTH],
                 [TAB_ID]
               )
            VALUES
               (
                 0,
                 100,
                 SCOPE_IDENTITY()
               )
            ]]>
    </CreateTab>
    <DeleteTab>
      <![CDATA[
              DELETE FROM [WIDGET_SETTINGS]
              WHERE [WIDGET_ID] IN (
                                     SELECT [ID]
                                     FROM [WIDGET]
                                     WHERE [COLUMN_ID] IN (
                                                           SELECT [ID] 
                                                           FROM [WIDGET_COLUMN] 
                                                           WHERE [TAB_ID] = @tabID
                                                           )
                                   )
              DELETE FROM [WIDGET] 
              WHERE [COLUMN_ID] IN (
                                     SELECT [ID] 
                                     FROM [WIDGET_COLUMN] 
                                     WHERE [TAB_ID] = @tabID
                                   )
              
              DELETE FROM [WIDGET_COLUMN] 
              WHERE [TAB_ID] = @tabID
              
              DELETE FROM [WIDGET_TAB] 
              WHERE [ID] = @tabID            
              ]]>
    </DeleteTab>
    <FixTabOrder>
        <![CDATA[
          UPDATE [WIDGET_TAB] SET
          [ORDER] = [ORDER]-1
          WHERE [USER_ID] = @userID AND
                [ORDER] > @tabID
          ]]>
    </FixTabOrder>
    <RenameTab>
      <![CDATA[
              UPDATE [WIDGET_TAB] SET
              [NAME] = @name
              WHERE [ID] = @tabID
              ]]>
    </RenameTab>
    <GetTabForColumn>
      <![CDATA[
      SELECT t.[ID]
      ,t.[NAME]
      ,t.[USER_ID]
      ,t.[ORDER]
      ,t.[ACTIVE]
  FROM [WIDGET_COLUMN] c, 
       [WIDGET_TAB] t
     WHERE t.[ID]=c.[TAB_ID] AND c.[ID] = @columnID
      ]]>
    </GetTabForColumn>
    <GetTabsForProfile>
      <![CDATA[
        SELECT [WIDGET_TAB].*
        FROM [WIDGET_TAB]
        WHERE [USER_ID] = @profileID
        ]]>
    </GetTabsForProfile>
    <CopyTabForUser>
      <![CDATA[
        INSERT INTO [WIDGET_TAB]
               (
                 [NAME],
                 [USER_ID],
                 [ORDER],
                 [ACTIVE]
               )
             VALUES
               (
                 @name,
                 @userID,
                 @order,
                 @active
               )            
        ]]>
    </CopyTabForUser>
    <GetLastInsertedTabID>
      <![CDATA[ SELECT IDENT_CURRENT('WIDGET_TAB') ]]>
    </GetLastInsertedTabID>
  </tab>
  <task>
    <CreateTask>
      <![CDATA[
        INSERT INTO [TASK](
          [NAME],
          [DESCRIPTION],
          [BLI_ID],
          [ESTIMATION],
          [PROGRESS],
          [PRIORITY],
          [TASK_STATE_ID]
        )VALUES(
          @name,
          @description,
          @bliID,
          @estimation,
          @progress,
          @priority,
          @taskStateID
        )
      ]]>
    </CreateTask>
    <GetLastInsertedTaskID>
      <![CDATA[ SELECT IDENT_CURRENT('TASK') ]]>
    </GetLastInsertedTaskID>
    <UpdateTask>
      <![CDATA[
        UPDATE 
          [TASK]
        SET 
          [NAME] = @name,
          [DESCRIPTION] = @description,
          [BLI_ID] = @bliID,
          [ESTIMATION] = @estimation,
          [PROGRESS] = @progress,
          [PRIORITY] = @priority,
          [TASK_STATE_ID] = @taskStateID,
          [CHANGED] = getdate()
        WHERE 
          [ID] = @taskID
      ]]>
    </UpdateTask>
    <DeleteTask>
      <![CDATA[
              DELETE FROM [USER_TASK]
              WHERE [TASK_ID] = @taskID
              
              DELETE FROM [TASK]
              WHERE [ID] = @taskID
              ]]>
    </DeleteTask>
    <GetAllTasks>
      <![CDATA[
        SELECT 
          [TASK].*,
          [TASK_STATE].[ID] AS [STATE_ID],
          [TASK_STATE].[NAME] AS [STATE_NAME]
        FROM 
          [PROJECT]
          JOIN [RELEASE] ON ([RELEASE].[PROJECT_ID] = [PROJECT].[ID])
          JOIN [SPRINT] ON ([SPRINT].[RELEASE_ID] = [RELEASE].[ID])
          JOIN [USER_STORY] ON ([USER_STORY].[SPRINT_ID] = [SPRINT].[ID])
          JOIN [BLI] ON ([BLI].[USER_STORY_ID] = [USER_STORY].[ID])
          JOIN [TASK] ON ([TASK].[BLI_ID] = [BLI].[ID])
          JOIN [TASK_STATE] ON ([TASK].[TASK_STATE_ID] = [TASK_STATE].[ID])
        WHERE
              ([PROJECT].[ID] = @projectID OR @projectID = -1)
          AND ([RELEASE].[ID] = @releaseID OR @releaseID = -1)
          AND ([SPRINT].[ID] = @sprintID OR @sprintID = -1)
          AND ([USER_STORY].[ID] = @userStoryID OR @userStoryID = -1)
          AND ([BLI].[ID] = @bliID OR @bliID = -1)
          AND ([TASK].[ID] = @taskID OR @taskID = -1)
      ]]>
    </GetAllTasks>
		<GetAllUsersTasks>
			<![CDATA[
					SELECT t.*, ts.[ID] AS [TASK_STATE_ID], ts.[NAME] AS [STATE_NAME]
					FROM [TASK] t
					INNER JOIN [USER_TASK] ut ON t.[ID] = ut.[TASK_ID]
					INNER JOIN [TASK_STATE] ts ON ts.[ID] = t.[TASK_STATE_ID]
					WHERE ut.[USER_ID] = @userID
            ]]>
		</GetAllUsersTasks>
    <GetAllTasksForBLI>
      <![CDATA[
            SELECT t.*,
                   s.[NAME] AS [STATE_NAME]
            FROM [TASK] t, 
                 [TASK_STATE] s
            WHERE t.TASK_STATE_ID = s.ID AND 
                  t.BLI_ID = @bliID
            ]]>
    </GetAllTasksForBLI>
    <GetAllTasksForBLIInTask>
      <![CDATA[
        SELECT t.*,
               s.[NAME] AS [STATE_NAME]
            FROM [TASK] t, 
                 [TASK_STATE] s
            WHERE t.TASK_STATE_ID = @stateID AND 
                  t.BLI_ID = @bliID
        ]]>
    </GetAllTasksForBLIInTask>
    <GetTaskByID>
      <![CDATA[
              SELECT t.*,
                     s.[NAME] AS [STATE_NAME]
            FROM [TASK] t, 
                 [TASK_STATE] s
            WHERE t.TASK_STATE_ID = s.ID AND 
                  t.ID = @taskID 
            ]]>
    </GetTaskByID>
    <AddAssociatedUser>
      <![CDATA[
            INSERT INTO [USER_TASK] ([TASK_ID], [USER_ID])
            VALUES (@taskID, @userID)
            ]]>
    </AddAssociatedUser>
    <RemoveAssociatedUser>
      <![CDATA[
            DELETE [USER_TASK]
            WHERE [TASK_ID] = @taskID AND [USER_ID] = @userID
            ]]>
    </RemoveAssociatedUser>
    <UpdateTaskState>
      <![CDATA[
        UPDATE [TASK]
        SET [TASK_STATE_ID] = (
                               SELECT ID
                               FROM [TASK_STATE]
                               WHERE [NAME] = @taskState
                              )
        WHERE [ID] = @taskID
        ]]>
    </UpdateTaskState>
    <UpdateTaskState>
      <![CDATA[
        UPDATE [TASK]
        SET [TASK_STATE_ID] = (
                               SELECT ID
                               FROM [TASK_STATE]
                               WHERE [NAME] = @taskState
                              )
        WHERE [ID] = @taskID
        ]]>
    </UpdateTaskState>
  </task>
  <team>
    <GetAllTeamsInProject>
      <![CDATA[
              SELECT [ID], 
                     [NAME], 
                     [PROJECT_ID]
              FROM [TEAM]
              WHERE [PROJECT_ID]=@projectID
              ]]>
    </GetAllTeamsInProject>
    <GetTeamByID>
      <![CDATA[
              SELECT [ID], 
                     [NAME], 
                     [PROJECT_ID]
              FROM [TEAM]
              WHERE [ID]=@teamID
              ]]>
    </GetTeamByID>
    <GetAllTeams>
      <![CDATA[
              SELECT [ID], 
                     [NAME], 
                     [PROJECT_ID]
              FROM [TEAM]
              ]]>
    </GetAllTeams>
    <GetTeamByUser>
      <![CDATA[
      SELECT t.[ID]
            ,t.[NAME]
      FROM  [TEAM_MEMBER] tm,
            [TEAM] t
      WHERE tm.[USER_ID] = @userID AND
               t.[ID] = tm.[TEAM_ID]
              ]]>
    </GetTeamByUser>
    <CreateTeam>
      <![CDATA[
        INSERT INTO [TEAM](
          [NAME],
          [PROJECT_ID]
        )VALUES(
          @name,
          @projectID
        )
      ]]>
    </CreateTeam>
    <GetLastInsertedTeamID>
      <![CDATA[ SELECT IDENT_CURRENT('TEAM')]]>
    </GetLastInsertedTeamID>
    <DeleteTeam>
      <![CDATA[
        DELETE FROM [TEAM] WHERE [ID] = @teamID
      ]]>
    </DeleteTeam>
  </team>
  <TeamMember>
    <GetUsers>
      <![CDATA[
        SELECT
          [TEAM_MEMBER].[ID] AS [ID],
          [USER].[ID] AS [USER_ID],
          [USER].[LOGIN] AS [USER_NAME],
          [USER].[FULLNAME] AS [FULLNAME],
          [TEAM].[ID] AS [TEAM_ID],
          [TEAM].[NAME] AS [TEAM_NAME],
          [ROLE].[ID] AS [ROLE_ID],
          [ROLE].[NAME] AS [ROLE_NAME]
        FROM
          [USER] 
          JOIN [TEAM_MEMBER] ON ([USER].[ID] = [TEAM_MEMBER].[USER_ID])
          JOIN [TEAM] ON ([TEAM].[ID] = [TEAM_MEMBER].[TEAM_ID])
          JOIN [ROLE] ON ([ROLE].[ID] = [TEAM_MEMBER].[ROLE_ID])
        WHERE
          ( [TEAM_MEMBER].[ID] = @userInRoleID OR @UserInRoleID = -1)
          AND ([TEAM].[PROJECT_ID] = @projectID OR @projectID = -1)
          AND ([TEAM].[ID] = @teamID OR @teamID = -1)
      ]]>
    </GetUsers>
    <GetUserInTeam>
      <![CDATA[
        SELECT * FROM [TEAM_MEMBER] WHERE [USER_ID] = @userID AND [TEAM_ID] = @teamID;
      ]]>
    </GetUserInTeam>
    <AddUserInRole>
      <![CDATA[
        INSERT INTO [TEAM_MEMBER](
          [USER_ID],
          [ROLE_ID],
          [TEAM_ID]
        )VALUES(
          @userID,
          @roleID,
          @teamID
        )
      ]]>
    </AddUserInRole>
    <EditUserInRole>
      <![CDATA[
        UPDATE [TEAM_MEMBER] SET
          [USER_ID] = @userID,
          [ROLE_ID] = @roleID,
          [TEAM_ID] = @teamID
        WHERE
          [ID] = @userInRoleID
      ]]>
    </EditUserInRole>
    <DeleteUserInRole>
      <![CDATA[
        DELETE FROM [TEAM_MEMBER] WHERE [ID] = @userInRoleID
      ]]>
    </DeleteUserInRole>
  </TeamMember>
  <threads>
    <GetAllThreadsForEntity>
      <![CDATA[
        SELECT [ID]
              ,[THREAD_NAME]
        FROM [COMMENT_THREAD]
        WHERE [ENTITY_ID] = @entityID AND
              [TYPE] = @type
      ]]>
    </GetAllThreadsForEntity>
    <GetThreadByID>
      <![CDATA[
        SELECT [THREAD_NAME]
              ,[TYPE]
              ,[ENTITY_ID]
        FROM [COMMENT_THREAD]
        WHERE [ID] = @threadID
      ]]>
    </GetThreadByID>
    <GetAllCommentsForThread>
      <![CDATA[
        SELECT [ID]
            ,[COMMENT]
            ,[USER_LOGIN]
            ,[DATE]
        FROM [COMMENT]
        WHERE [THREAD_ID] = @threadID
      ]]>
    </GetAllCommentsForThread>
    <GetSubCommentsForComment>
      <![CDATA[
       SELECT [ID]
            ,[COMMENT]
            ,[USER_LOGIN]
            ,[DATE]
            ,[THREAD_ID]
        FROM [COMMENT]
        WHERE COMMENT_ID = @commentID
      ]]>
    </GetSubCommentsForComment>
    <InsertThread>
      <![CDATA[
      INSERT INTO [COMMENT_THREAD]
           ([THREAD_NAME]
           ,[TYPE]
           ,[ENTITY_ID])
     VALUES
           (@name
           ,@type
           ,@entityID)
      ]]>
    </InsertThread>
    <GetLastInsertedThreadID>
      <![CDATA[SELECT IDENT_CURRENT('COMMENT_THREAD')]]>
    </GetLastInsertedThreadID>
    <GetLastInsertedCommentID>
      <![CDATA[SELECT IDENT_CURRENT('COMMENT')]]>
    </GetLastInsertedCommentID>
    <InsertComment>
      <![CDATA[
      INSERT INTO [COMMENT]
           ([THREAD_ID]
           ,[COMMENT_ID]
           ,[COMMENT]
           ,[USER_LOGIN]
           ,[DATE])
     VALUES
           (@threadID
           ,@commentID
           ,@comment
           ,@userID
           ,@date)
      ]]>
    </InsertComment>
    <InsertComment2>
      <![CDATA[
      INSERT INTO [COMMENT]
           ([THREAD_ID]
           ,[COMMENT]
           ,[USER_LOGIN]
           ,[DATE])
     VALUES
           (@threadID
           ,@comment
           ,@userID
           ,@date)
      ]]>
    </InsertComment2>
    <UpdateThread>
      <![CDATA[
      UPDATE [COMMENT_THREAD]
      SET [THREAD_NAME] = @name
          ,[TYPE] = @type
          ,[ENTITY_ID] = @entityID
      WHERE [ID] = @ID
      ]]>
    </UpdateThread>
    <DeleteThread>
      <![CDATA[
      DELETE FROM [COMMENT_THREAD]
      WHERE [ID] = @ID
      ]]>
    </DeleteThread>
    <UpdateComment>
      <![CDATA[
      UPDATE [COMMENT]
      SET [THREAD_ID] = @threadID
        ,[COMMENT_ID] = @commentID
        ,[COMMENT] = @comment
        ,[USER_LOGIN] = @userID
        ,[DATE] = @date
      WHERE [ID] = @ID
      ]]>
    </UpdateComment>
    <DeleteComment>
      <![CDATA[
      DELETE FROM [COMMENT]
      WHERE [ID] = @ID
      ]]>
    </DeleteComment>
    <GetCommentByID>
      <![CDATA[
      SELECT [THREAD_ID]
      ,[COMMENT]
      ,[USER_LOGIN]
      ,[DATE]
      FROM [COMMENT]
      WHERE [ID] = @ID
      ]]>
    </GetCommentByID>
    <GetCommentsForEntity>
      <![CDATA[
      SELECT [ID]
            ,[COMMENT]
            ,[USER_LOGIN]
            ,[DATE]
            ,[THREAD_ID]
        FROM [COMMENT]
        WHERE [THREAD_ID] in (SELECT [ID]
        FROM [COMMENT_THREAD]
        WHERE [ENTITY_ID] = @ID AND
              [TYPE] = @type)
      ]]>
    </GetCommentsForEntity>
  </threads>
  <user>
    <GetUsers>
      <![CDATA[
        SELECT 
          [USER].*
        FROM 
          [USER]
        WHERE
              ([ID] = @ID OR @ID = -1)
          AND ([LOGIN] like '%'+@LOGIN+'%' or @LOGIN is null)
          AND ([FULLNAME] like '%'+@FULLNAME+'%' or @FULLNAME is null)
          AND ([EMAIL] like '%'+@EMAIL+'%' or @EMAIL is null)
          AND ([ENABLED] = @ENABLED OR @ENABLED is null)
          AND ([TELEPHONE] like '%'+@TELEPHONE+'%' or @TELEPHONE is null)
          AND ([IM] like '%'+@IM+'%' or @IM is null)
          AND ([DEFAULT_ROLE_ID] = @DEFAULT_ROLE_ID OR @DEFAULT_ROLE_ID = -1)
          AND ([IS_PROFILE] = 0)
      ]]>
    </GetUsers>
    <GetTeamMembers>
      <![CDATA[
              SELECT 
                [USER].*
              FROM 
                [USER] LEFT JOIN [TEAM_MEMBER] ON ([USER].[ID] = [TEAM_MEMBER].[USER_ID])
              WHERE 
                    ([TEAM_MEMBER].[TEAM_ID] = @teamID OR @teamID = -1)
                AND ([TEAM_MEMBER].[ROLE_ID] = @roleID OR @roleID = -1)
                AND [USER].[ENABLED] = 1
              ]]>
    </GetTeamMembers>
    <GetUsersExceptTaskUsers>
      <![CDATA[
              SELECT [USER].[ID], 
                     [USER].[Login], 
                     [USER].[FullName], 
                     [USER].[Email], 
                     [USER].[Password], 
                     [USER].[Enabled]
                FROM [USER]
                WHERE [USER].[TEAM_ID] = @teamID AND [USER].[Enabled] = 1
                EXCEPT
                SELECT [USER].[ID], 
                     [USER].[Login], 
                     [USER].[FullName], 
                     [USER].[Email], 
                     [USER].[Password], 
                     [USER].[Enabled]
                FROM [USER]
                INNER JOIN [USER_TASK] ON [USER_TASK].[USER_ID] = [USER].[ID]
                WHERE [USER_TASK].[TASK_ID] = @taskID
              ]]>
    </GetUsersExceptTaskUsers>
    <GetUsersExceptTaskUsers2>
      <![CDATA[
              SELECT [USER].[ID], 
                     [USER].[Login], 
                     [USER].[FullName], 
                     [USER].[Email], 
                     [USER].[Password], 
                     [USER].[Enabled]
                FROM [USER]
                WHERE [USER].[Enabled] = 1
                EXCEPT
                SELECT [USER].[ID], 
                     [USER].[Login], 
                     [USER].[FullName], 
                     [USER].[Email], 
                     [USER].[Password], 
                     [USER].[Enabled]
                FROM [USER]
                INNER JOIN [USER_TASK] ON [USER_TASK].[USER_ID] = [USER].[ID]
                WHERE [USER_TASK].[TASK_ID] = @taskID
              ]]>
    </GetUsersExceptTaskUsers2>
    <GetUsersByTask>
      <![CDATA[
        SELECT 
          [USER].*
        FROM 
          [USER]
          INNER JOIN [USER_TASK] ON [USER_TASK].[USER_ID] = [USER].[ID]
        WHERE 
          [USER_TASK].[TASK_ID] = @taskID
      ]]>
    </GetUsersByTask>
  </user>
  <userStory>
    <CreateUserStory>
      <![CDATA[
        INSERT INTO [USER_STORY](
          [NAME],
          [DESCRIPTION],
          [ESTIMATION],
          [SPRINT_ID],
          [USER_STORY_STATE_ID],
          [PROGRESS],
          [PRIORITY]
        )VALUES(
          @name,
          @description,
          @estimation,
          @sprintId,
          @userStoryStateId,
          @progress,
          @priority
         )
      ]]>
    </CreateUserStory>
    <GetLastInsertedUserStoryID>
      <![CDATA[ SELECT IDENT_CURRENT('USER_STORY') ]]>
    </GetLastInsertedUserStoryID>
    <UpdateUserStory>
      <![CDATA[
        UPDATE 
          [USER_STORY]
        SET 
          [NAME] = @name,
          [DESCRIPTION] = @description,
          [SPRINT_ID] = @sprintId,
          [ESTIMATION] = @estimation,
          [PROGRESS] = @progress,
          [USER_STORY_STATE_ID] = @userStoryStateId
        WHERE [ID] = @id
      ]]>
    </UpdateUserStory>
    <UpdateUserStory2>
      <![CDATA[
        UPDATE 
          [USER_STORY]
        SET
          [NAME] = @name,
          [DESCRIPTION] = @description,
          [ESTIMATION] = @estimation,
          [PROGRESS] = @progress,
          [USER_STORY_STATE_ID] = @userStoryStateId,
          [SPRINT_ID] = @sprintID
        WHERE [ID] = @id
      ]]>
    </UpdateUserStory2>
    <DeleteUserStory>
      <![CDATA[
              DELETE FROM [USER_STORY]
              WHERE [ID] = @userStoryId
              ]]>
    </DeleteUserStory>
    <GetAllUserStories>
      <![CDATA[
        SELECT 
          [USER_STORY].*,
          [USER_STORY_STATE].[ID] AS [STATE_ID],
          [USER_STORY_STATE].[NAME] AS [STATE_NAME]
        FROM 
          [PROJECT]
          JOIN [RELEASE] ON ([RELEASE].[PROJECT_ID] = [PROJECT].[ID])
          JOIN [SPRINT] ON ([SPRINT].[RELEASE_ID] = [RELEASE].[ID])
          JOIN [USER_STORY] ON ([USER_STORY].[SPRINT_ID] = [SPRINT].[ID])
          JOIN [USER_STORY_STATE] ON ([USER_STORY].[USER_STORY_STATE_ID] = [USER_STORY_STATE].[ID])
        WHERE
              ([PROJECT].[ID] = @projectID OR @projectID = -1)
          AND ([RELEASE].[ID] = @releaseID OR @releaseID = -1)
          AND ([SPRINT].[ID] = @sprintID OR @sprintID = -1)
          AND ([USER_STORY].[ID] = @userStoryID OR @userStoryID = -1)
          AND (@bliID = -1 OR EXISTS(
            SELECT 1 FROM [BLI] WHERE [BLI].[ID] = @bliID 
            AND [BLI].[USER_STORY_ID] = [USER_STORY].[ID]
          ))
      ]]>
    </GetAllUserStories>
    <GetUserStoryById>
      <![CDATA[
              SELECT us.*
                     st.[NAME] as STATE_NAME
              FROM [USER_STORY] us,
                   [USER_STORY_STATE] st
              WHERE st.[ID] = us.[USER_STORY_STATE_ID] AND
                    us.[ID] = @id
              ]]>
    </GetUserStoryById>
    <GetUserStoryByBli>
      <![CDATA[
              SELECT us.*,
                     st.[NAME] as STATE_NAME
              FROM [USER_STORY] us, 
                   [USER_STORY_STATE] st,
                   [BLI] b
              WHERE b.[ID] = @bliID AND
                    b.[USER_STORY_ID] = us.[ID] AND
                    us.[USER_STORY_STATE_ID] = st.[ID]
              ]]>
    </GetUserStoryByBli>
    <GetAllUserStoriesForSprint>
      <![CDATA[
              SELECT us.*,
                     st.[NAME] as [STATE_NAME]
              FROM [USER_STORY] us,
                   [USER_STORY_STATE] st
              WHERE us.[SPRINT_ID] = @sprintID AND
                    us.[USER_STORY_STATE_ID] = st.[ID]
              ]]>
    </GetAllUserStoriesForSprint>
    <GetAllUserStoriesForRelease>
      <![CDATA[
              SELECT us.*,
                     st.[NAME] as [STATE_NAME]
              FROM [USER_STORY] us JOIN [SPRINT] s ON us.[SPRINT_ID] = s.[ID],
                   [USER_STORY_STATE] st
              WHERE s.[RELEASE_ID] = @releaseID AND
                    us.[USER_STORY_STATE_ID] = st.[ID]
              ]]>
    </GetAllUserStoriesForRelease>
    <GetAllUserStoriesForProject>
      <![CDATA[
              SELECT us.*,
                     st.[NAME] as [STATE_NAME]
              FROM [USER_STORY] us JOIN [SPRINT] s ON us.[SPRINT_ID] = s.[ID] 
                                   JOIN [RELEASE] r ON s.[RELEASE_ID] = r.[ID],
                   [USER_STORY_STATE] st
              WHERE r.[PROJECT_ID] = @projectID AND
                    us.[USER_STORY_STATE_ID] = st.[ID]
              ]]>
    </GetAllUserStoriesForProject>
    <GetUnassignedStories>
      <![CDATA[
              SELECT us.*,
                     st.[NAME] as [STATE_NAME]
              FROM [USER_STORY] us INNER JOIN [USER_STORY_STATE] st ON st.[ID] = us.[USER_STORY_STATE_ID]
              WHERE us.[SPRINT_ID] is null
              ]]>
    </GetUnassignedStories>
    <GetAllUserStories>
      <![CDATA[
              SELECT us.*,
                     st.[NAME] as [STATE_NAME]
              FROM [USER_STORY] us,
                   [USER_STORY_STATE] st
              WHERE us.[USER_STORY_STATE_ID] = st.[ID]
              ]]>
    </GetAllUserStories> 
    <UpdateUserStorySprint>
      <![CDATA[
              UPDATE [USER_STORY]
              SET [SPRINT_ID] = @sprintID
              WHERE [ID] = @userStoryID
              ]]>
    </UpdateUserStorySprint>
  </userStory>
  <widgets>
    <GetWidgetByName>
      <![CDATA[
              SELECT [ID], 
                     [NAME]
              FROM [WIDGET]
              WHERE [NAME]=@widgetName
              ]]>
    </GetWidgetByName>
    <GetWidgetByID>
      <![CDATA[
              SELECT [WIDGET].[ID], 
                     [WIDGET].[ORDER], 
                     [WIDGET_TYPE].[NAME] as 'TYPE',
                     [WIDGET].[TYPE_ID] 
              FROM [WIDGET] INNER JOIN [WIDGET_TYPE] ON [WIDGET_TYPE].[ID] = [WIDGET].[TYPE_ID]
              WHERE [WIDGET].[ID] = @widgetID;
              ]]>
    </GetWidgetByID>
    <GetWidgets>
      <![CDATA[
              SELECT [WIDGET].[ID], 
                     [WIDGET].[ORDER], 
                     [WIDGET_TYPE].[NAME] as 'TYPE',
                     [WIDGET].[TYPE_ID] 
              FROM [WIDGET] INNER JOIN [WIDGET_TYPE] ON [WIDGET_TYPE].[ID] = [WIDGET].[TYPE_ID]
              WHERE [COLUMN_ID] = @columnID
              ORDER BY [ORDER] ASC
              ]]>
    </GetWidgets>
    <CreateNewWidget>
      <![CDATA[
              UPDATE [WIDGET]
              SET [ORDER] = [ORDER] + 1
              WHERE [COLUMN_ID] = @columnID
              
              INSERT INTO [WIDGET]
                     (
                       [TYPE_ID],
                       [USER_ID],
                       [COLUMN_ID],
                       [ORDER]
                     )
              VALUES
                (
                  @typeID,
                  @userID,
                  @columnID,
                  @order
                  ) 
              ]]>
    </CreateNewWidget>
    <DeleteWidget>
      <![CDATA[
              -- posunieme ostatne widgety v columne
              UPDATE [WIDGET] SET
              [ORDER] = [ORDER] - 1
              WHERE [COLUMN_ID] IN (
                                    SELECT [COLUMN_ID] 
                                    FROM [WIDGET] WHERE [ID] = @widgetID
                                   )
                                AND [ORDER] > @order
              
              -- zmazeme nastavenia widgetu
              DELETE FROM [WIDGET_SETTINGS]
              WHERE [WIDGET_ID] = @widgetID
              
              -- zmazeme tento widget
              DELETE FROM [WIDGET]
              WHERE [ID] = @widgetID
              ]]>
    </DeleteWidget>
    <SwitchWidgets>
      <![CDATA[
        UPDATE [WIDGET] SET
              [ORDER] = [ORDER] + 1
              WHERE [ID] = @widget1ID
              
        UPDATE [WIDGET] SET
              [ORDER] = [ORDER] - 1
              WHERE [ID] = @widget2ID      
      ]]>
    </SwitchWidgets>
  </widgets>
  <WidgetRights>
    <GetWidgetsRights>
      <![CDATA[
        SELECT
          [POSSIBLE].[ROLE_ID] AS [ROLE_ID],
          [POSSIBLE].[WIDGET_TYPE_ID] AS [WIDGET_TYPE_ID],
          [POSSIBLE].[ROLE_NAME] AS [ROLE_NAME],
          [POSSIBLE].[WIDGET_TYPE_NAME] AS [WIDGET_TYPE_NAME],
          COALESCE([WIDGET_RIGHTS].[READ],0) AS [READ_NN],
          COALESCE([WIDGET_RIGHTS].[WRITE],0) AS [WRITE_NN],
	        (CASE COALESCE([WIDGET_RIGHTS].[ID],-1) WHEN -1 THEN 0 ELSE 1 END) AS [EXISTING],
          COALESCE([WIDGET_RIGHTS].[ID],-1) AS [ID]
        FROM
	        (
		        SELECT 
		          [ROLE].[ID] AS [ROLE_ID],
		          [WIDGET_TYPE].[ID] AS [WIDGET_TYPE_ID],
		          [ROLE].[NAME] AS [ROLE_NAME],
		          [WIDGET_TYPE].[NAME] AS [WIDGET_TYPE_NAME]
		        FROM [ROLE] CROSS JOIN [WIDGET_TYPE]
		        WHERE
			        ([ROLE].[ID] = @roleID OR @roleID = -1)
			        AND ([WIDGET_TYPE].[ID] = @widgetTypeID OR @widgetTypeID = -1)
	        )as POSSIBLE 
	        LEFT OUTER JOIN [WIDGET_RIGHTS] ON 
	        (
		        [POSSIBLE].[ROLE_ID] = [WIDGET_RIGHTS].[ROLE_ID]
		        AND [POSSIBLE].[WIDGET_TYPE_ID] = [WIDGET_RIGHTS].[WIDGET_TYPE_ID]
	        )
        ORDER BY 
          [WIDGET_TYPE_NAME], [ROLE_NAME]
      ]]>
    </GetWidgetsRights>
    <UpdateWriteRight>
      <![CDATA[
        UPDATE [WIDGET_RIGHTS] SET [WRITE] = @write WHERE [ID] = @rightID
      ]]>
    </UpdateWriteRight>
    <AddViewRight>
      <![CDATA[
        INSERT INTO [WIDGET_RIGHTS](
          ROLE_ID,
          WIDGET_TYPE_ID
        ) VALUES (
          @roleID,
          @widgetTypeID
        )
      ]]>
    </AddViewRight>
    <RemoveViewRight>
      <![CDATA[
        DELETE FROM [WIDGET_RIGHTS] WHERE [ID] = @rightID
      ]]>
    </RemoveViewRight>
    <GetUserWidgetRights>
      <![CDATA[
        SELECT 
          [WIDGET_TYPE].[ID] AS [WIDGET_TYPE_ID],
          [WIDGET_RIGHTS].[WRITE] AS [CAN_WRITE]
        FROM 
          [WIDGET_TYPE] JOIN [WIDGET_RIGHTS] ON [WIDGET_TYPE].[ID] = [WIDGET_RIGHTS].[WIDGET_TYPE_ID]
        WHERE 
          (-- userove roly maju nejaky zaznam v tabulke widget rights
            [WIDGET_RIGHTS].[ROLE_ID] IN (
              SELECT DISTINCT [TEAM_MEMBER].[ROLE_ID]
              FROM [USER] 
              JOIN [TEAM_MEMBER] ON ([USER].[ID] =[TEAM_MEMBER].[USER_ID])
              WHERE [USER].[ID] = @userID
            )OR [WIDGET_RIGHTS].[ROLE_ID] IN (
              SELECT [USER].[DEFAULT_ROLE_ID] FROM [USER] WHERE [USER].[ID] = @userID
            )
          )
          AND [WIDGET_TYPE].[ID] = @widgetTypeID -- a je to tento widget
          AND [WIDGET_TYPE].[ENABLED] = 1
      ]]>
    </GetUserWidgetRights>
  </WidgetRights>
  <widgetType>
    <GetAllAvailableWidgets>
      <![CDATA[
        SELECT DISTINCT
          [WIDGET_TYPE].*
        FROM 
          [WIDGET_TYPE] JOIN [WIDGET_RIGHTS] ON [WIDGET_TYPE].[ID] = [WIDGET_RIGHTS].[WIDGET_TYPE_ID]
        WHERE 
          (
            [WIDGET_RIGHTS].[ROLE_ID] IN (
              SELECT DISTINCT [TEAM_MEMBER].[ROLE_ID]
              FROM [USER] 
              JOIN [TEAM_MEMBER] ON ([USER].[ID] =[TEAM_MEMBER].[USER_ID])
              WHERE [USER].[ID] = @userID
            )OR [WIDGET_RIGHTS].[ROLE_ID] IN (
              SELECT [USER].[DEFAULT_ROLE_ID] FROM [USER] WHERE [USER].[ID] = @userID
            )
          )AND [WIDGET_TYPE].[ENABLED] = 1
        ORDER BY [WIDGET_TYPE].[NAME] ASC
      ]]>
    </GetAllAvailableWidgets>
    <GetAllWidgetsTypes>
      <![CDATA[
        SELECT 
          [WIDGET_TYPE].*
        FROM
          [WIDGET_TYPE]
      ]]>
    </GetAllWidgetsTypes>
    <GetWidgetTypeByID>
      <![CDATA[
        SELECT 
          [WIDGET_TYPE].* 
        FROM 
          [WIDGET_TYPE] 
        WHERE 
          [ID] = @widgetTypeID
      ]]>
    </GetWidgetTypeByID>
    <SaveWidgetType>
      <![CDATA[
        UPDATE [WIDGET_TYPE] SET 
          [NAME] = @Name,
          [ENABLED] = @Enabled
        WHERE 
          [ID] = @ID
      ]]>
    </SaveWidgetType>
    <CreateWidgetType>
      <![CDATA[
        INSERT INTO [WIDGET_TYPE](
          [NAME],
          [ENABLED]
        )VALUES(
          @Name,
          @Enabled
        )
      ]]>
    </CreateWidgetType>
  </widgetType>
  <widgetSettings>
    <LoadSettings>
      <![CDATA[
              SELECT [NAME], [VALUE]
              FROM [WIDGET_SETTINGS]
              WHERE [WIDGET_ID]=@widgetID
              ]]>
    </LoadSettings>
    <SaveSettings>
      <![CDATA[
              UPDATE [WIDGET_SETTINGS]
              SET   [VALUE]=@settingValue
              WHERE [WIDGET_ID]=@widgetID AND
                    [NAME]=@settingName
              ]]>
    </SaveSettings>
    <SaveDefaultSettings>
      <![CDATA[
              INSERT INTO [WIDGET_SETTINGS]
                (
                  [WIDGET_ID],
                  [NAME], 
                  [VALUE]
                )
              VALUES
                (
                  @widgetID,
                  @settingName,
                  @settingValue
                )
              ]]>
    </SaveDefaultSettings>
    <UpdateSetting>
      <![CDATA[
              UPDATE [WIDGET_SETTINGS]
              SET [VALUE]=@settingValue
              WHERE [WIDGET_ID] = @widgetID AND 
                    [NAME] = @settingName
            ]]>
    </UpdateSetting>
    <SaveSetting>
      <![CDATA[
              INSERT INTO [WIDGET_SETTINGS]
                  (
                    [WIDGET_ID],
                    [NAME],
                    [VALUE]
                  )
              VALUES
                  (
                    @widgetID,
                    @settingName,
                    @settingValue
                  )
              ]]>
    </SaveSetting>
    <DeleteSettings>
      <![CDATA[
              DELETE FROM [WIDGET_SETTINGS] 
              WHERE [WIDGET_ID] = @widgetID AND
                    [NAME] = @settingName
              ]]>
    </DeleteSettings>
  </widgetSettings>
  <workProgress>
		<GetProgressByID>
			<![CDATA[
						  SELECT [ID]
                    ,[USER_ID]
                    ,[TASK_ID]
                    ,[DATE]
                    ,[STORY_POINTS]
                    ,[NOTE]
                    ,[SPRINT_ID]
                    ,[TASK_STATE_ID] 
              FROM [WORK_PROGRESS]
							WHERE [ID]=@progressID
              ]]>
		</GetProgressByID>
		<EditWorkProgress>
			<![CDATA[
              UPDATE [WORK_PROGRESS]
							SET [DATE]=@date, [STORY_POINTS]=@storyPoints, [NOTE]=@note
							WHERE [ID]=@id
              ]]>
		</EditWorkProgress>
		<DeleteWorkProgress>
			<![CDATA[
              DELETE FROM [WORK_PROGRESS] 
              WHERE [ID] = @progressID
              ]]>
		</DeleteWorkProgress>
    <GetProgressForUser>
      <![CDATA[
              SELECT [ID]
                    ,[USER_ID]
                    ,[TASK_ID]
                    ,[DATE]
                    ,[STORY_POINTS]
                    ,[NOTE]
                    ,[SPRINT_ID]
                    ,[TASK_STATE_ID]
                FROM [WORK_PROGRESS]
                WHERE [USER_ID] = @userID AND 
										  [DATE] BETWEEN @from AND @to
								ORDER BY [DATE] DESC
              ]]>
    </GetProgressForUser>
    <AddWorkProgress>
      <![CDATA[
              INSERT INTO [WORK_PROGRESS]
                       ([USER_ID]
                       ,[TASK_ID]
                       ,[DATE]
                       ,[STORY_POINTS]
                       ,[NOTE]
                       ,[SPRINT_ID]
                       ,[TASK_STATE_ID])
                 VALUES
                       (@userID, @taskID, @date, @storyPoints, @note, @sprintID, @taskStateID)
              ]]>
    </AddWorkProgress>
    <UpdateProgressForEntities>
      <![CDATA[
UPDATE  [TASK]
   SET [PROGRESS] = ((Select [PROGRESS] from  [TASK] WHERE [ID] = @taskID) + @storyPoints)
 WHERE [ID] = @taskID
 
declare @bli int;
SELECT @bli = [BLI_ID] from  [TASK] WHERE [ID] = @taskID;


UPDATE  [BLI]
   SET [PROGRESS] = ((Select [PROGRESS] from  [BLI] WHERE [ID] = @bli) + @storyPoints)
 WHERE [ID] = @bli
 
 declare @us int;
SELECT @us = [USER_STORY_ID] from  [BLI] WHERE [ID] = @bli;


UPDATE  [USER_STORY]
   SET [PROGRESS] = ((Select [PROGRESS] from  [USER_STORY] WHERE [ID] = @us) + @storyPoints)
 WHERE [ID] = @us
 
 declare @spr int;
SELECT @spr = [SPRINT_ID] from  [USER_STORY] WHERE [ID] = @us;


UPDATE  [SPRINT]
   SET [PROGRESS] = ((Select [PROGRESS] from  [SPRINT] WHERE [ID] = @spr) + @storyPoints)
 WHERE [ID] = @spr
 
 declare @rel int;
SELECT @rel = [RELEASE_ID] from  [SPRINT] WHERE [ID] = @spr;

UPDATE  [RELEASE]
   SET [PROGRESS] = ((Select [PROGRESS] from  [RELEASE] WHERE [ID] = @rel) + @storyPoints)
 WHERE [ID] = @rel
 
 declare @pro int;
SELECT @pro = [PROJECT_ID] from  [RELEASE] WHERE [ID] = @rel;

UPDATE  [PROJECT]
   SET [PROGRESS] = ((Select [PROGRESS] from  [PROJECT] WHERE [ID] = @pro) + @storyPoints)
 WHERE [ID] = @pro
      ]]>
    </UpdateProgressForEntities>
  </workProgress>
  <availability>
    <GetAvailabilityForTeam>
      <![CDATA[
      SELECT [ID]
      ,[USER_ID]
      ,[FROM]
      ,[TO]
      ,[NAME]
      ,[DESCRIPTION]
    FROM  [CALENDAR_EVENT]
    WHERE [USER_ID] in (SELECT [USER_ID]
      FROM  [TEAM_MEMBER]
      WHERE [TEAM_ID] = @teamID)
      AND (
      ([FROM] >= @from AND [TO] <= @to) 
      OR ([FROM] <= @from AND [TO] >= @from)
      OR ([FROM] <= @to AND [TO] >= @to)
      OR ([FROM] >= @from AND [TO] <= @to)
      )
      ]]>
    </GetAvailabilityForTeam>
    <GetAvailabilityForUser>
      <![CDATA[
      SELECT [ID]
      ,[USER_ID]
      ,[FROM]
      ,[TO]
      ,[NAME]
      ,[DESCRIPTION]
    FROM  [CALENDAR_EVENT]
    WHERE [USER_ID] = @userID
      AND 
      (
      ([FROM] >= @from AND [TO] <= @to) 
      OR ([FROM] <= @from AND [TO] >= @from)
      OR ([FROM] <= @to AND [TO] >= @to)
      OR ([FROM] >= @from AND [TO] <= @to)
      )
      ]]>
    </GetAvailabilityForUser>
    <GetAvailabilities>
      <![CDATA[
      SELECT [ID]
      ,[USER_ID]
      ,[FROM]
      ,[TO]
      ,[NAME]
      ,[DESCRIPTION]
    FROM  [CALENDAR_EVENT]
    WHERE (
      ([FROM] >= @from AND [TO] <= @to) 
      OR ([FROM] <= @from AND [TO] >= @from)
      OR ([FROM] <= @to AND [TO] >= @to)
      OR ([FROM] <= @from AND [TO] >= @to)
      )
		ORDER BY [FROM] ASC
      ]]>
    </GetAvailabilities>
    <CreateAvailability>
      <![CDATA[
      INSERT INTO  [CALENDAR_EVENT]
           ([USER_ID]
           ,[FROM]
           ,[TO]
           ,[NAME]
           ,[DESCRIPTION])
     VALUES
           (@userID
           ,@from
           ,@to
           ,@name
           ,@desc)
      ]]>
    </CreateAvailability>
    <GetLastInsertedAvailabilityID>
      <![CDATA[
      SELECT IDENT_CURRENT('CALENDAR_EVENT')
      ]]>
    </GetLastInsertedAvailabilityID>
    <GetAvailabilityByID>
      <![CDATA[
      SELECT [ID]
      ,[USER_ID]
      ,[FROM]
      ,[TO]
      ,[NAME]
      ,[DESCRIPTION]
    FROM  [CALENDAR_EVENT]
    WHERE [ID] = @ID
      ]]>
    </GetAvailabilityByID>
    <UpdateAvailability>
      <![CDATA[
      UPDATE  [CALENDAR_EVENT]
    SET [USER_ID] = @userID
      ,[FROM] = @from
      ,[TO] = @to
      ,[NAME] = @name
      ,[DESCRIPTION] = @desc
    WHERE [ID] = @ID
      ]]>
    </UpdateAvailability>
    <DeleteAvailability>
      <![CDATA[
      DELETE FROM  [CALENDAR_EVENT]
      WHERE [ID] = @ID
      ]]>
    </DeleteAvailability>
  </availability>
</commands>
